---
redirect_from:
  - /recipes/column-based-access
---

# Enforcing column-based access

## Use case

We want to manage user access to different data depending on a database
relationship. In the recipe below, we will manage supplier access to their
products. A supplier can't see other supplier's products.

## Data modeling

To implement column-based access, we will use supplier's email from a
[JSON Web Token](/product/auth), and the
[`queryRewrite`](/product/auth/context#using-queryrewrite) extension point to
manage data access.

We have `products` and `suppliers` cubes with a `many_to_one` relationship from
products to suppliers:

<CodeTabs>

```yaml
cubes:
  - name: products
    sql_table: products

    joins:
      - name: suppliers
        relationship: many_to_one
        sql: "{CUBE}.supplier_id = {suppliers.id}"

    dimensions:
      - name: name
        sql: name
        type: string
```

```javascript
cube(`products`, {
  sql_table: `products`,

  joins: {
    suppliers: {
      relationship: `many_to_one`,
      sql: `${CUBE}.supplier_id = ${suppliers.id}`,
    },
  },

  dimensions: {
    name: {
      sql: `name`,
      type: `string`,
    },
  },
});
```

</CodeTabs>

<CodeTabs>

```yaml
cubes:
  - name: suppliers
    sql_table: suppliers

    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true

      - name: email
        sql: email
        type: string
```

```javascript
cube(`suppliers`, {
  sql_table: `suppliers`,

  dimensions: {
    id: {
      sql: `id`,
      type: `string`,
      primary_key: true,
    },

    email: {
      sql: `email`,
      type: `string`,
    },
  },
});
```

</CodeTabs>

## Configuration

Let's add the supplier email filter if a query includes any dimensions or
measures from the `products` cube:

```javascript
module.exports = {
  queryRewrite: (query, { securityContext }) => {
    const cubeNames = [
      ...(query.dimensions || []),
      ...(query.measures || []),
    ].map((e) => e.split(".")[0]);

    if (cubeNames.includes("products")) {
      if (!securityContext.email) {
        throw new Error("No email found in Security Context!");
      }

      query.filters.push({
        member: `suppliers.email`,
        operator: "equals",
        values: [securityContext.email],
      });
    }

    return query;
  },
};
```

## Query

To get the supplier's products, we will send two identical requests with
different emails inside JWTs.

```json
{
  "iat": 1000000000,
  "exp": 5000000000,
  "email": "purus.accumsan@Proin.org"
}
```

```json
{
  "iat": 1000000000,
  "exp": 5000000000,
  "email": "gravida.sit.amet@risus.net"
}
```

## Result

We have received different data depending on the supplier's email.

```javascript
// purus.accumsan@Proin.org
[
  {
    "products.name": "Awesome Soft Salad",
  },
  {
    "products.name": "Rustic Granite Gloves",
  },
];
```

```javascript
// gravida.sit.amet@risus.net
[
  {
    "products.name": "Incredible Granite Cheese",
  },
];
```

## Source code

Please feel free to check out the
[full source code](https://github.com/cube-js/cube/tree/master/examples/recipes/column-based-access)
or run it with the `docker-compose up` command. You'll see the result, including
queried data, in the console.
